#### USE THIS
## (EYB - BYB)/BYB * 100
## Rose has notes on her choice for this calculation
pct_change_ds <- endowment_data %>%
  filter(!is.na(BeginningYearBalanceAmt)) %>%
  mutate(change = EndYearBalanceAmt - BeginningYearBalanceAmt,
         pct_change = change/BeginningYearBalanceAmt * 100) %>%
  arrange(desc(pct_change)) %>%
  left_join(names, by = "EIN")

#### USE THIS
## (EYB - INV - BYB)/BYB * 100
## Rose has notes on her choice for this calculation
pct_change_inv_ds <- endowment_data %>%
  filter(!is.na(BeginningYearBalanceAmt)) %>%
  mutate(InvestmentEarningsOrLossesAmt = ifelse(is.na(InvestmentEarningsOrLossesAmt), 0, InvestmentEarningsOrLossesAmt)) %>%
  mutate(change = EndYearBalanceAmt - InvestmentEarningsOrLossesAmt - BeginningYearBalanceAmt,
         pct_change = change/BeginningYearBalanceAmt * 100) %>%
  arrange(desc(pct_change)) %>%
  left_join(names, by = "EIN")

# Basic summary stats
pct_change_ds %>%   
  filter(!is.na(pct_change) & pct_change != Inf) %>%
  summarize(avg_pct_change = mean(pct_change),
            median_pct_change = median(pct_change),
            sd_pct_change = sd(pct_change))

pct_change_ds %>%   
  filter(!is.na(pct_change) & pct_change != Inf) %>%
  group_by(EIN) %>%
  summarize(avg_pct_change = mean(pct_change),
            median_pct_change = median(pct_change),
            sd_pct_change = sd(pct_change))

# Basic histogram summarizing it
ggplot(pct_change_ds, aes(x = pct_change)) +
  geom_histogram(binwidth = 20) + 
  xlab("% Change\n(EYB - BYB) / BYB * 100") + 
  ggtitle(label = "Percentage of Change in Endowment Balance", subtitle = "Red Line indicates 100%") +
  theme_classic() + 
  geom_vline(xintercept = 100, color = "maroon", linetype = "dotted")

## Histogram with cutoff of 200%
ggplot(pct_change_ds, aes(x = pct_change)) +
  geom_histogram(binwidth = 5) + 
  xlab("% Change\n(EYB - BYB) / BYB * 100") + 
  xlim(-100, 200) + 
  ggtitle(label = "Percentage of Change in Endowment Balance") +
  theme_classic()

pct_change_ds %>%
  filter(pct_change != Inf) %>%
  select(organization_name, fiscal_year, pct_change) %>%
  make_table(title = "Percentage of Change in Endowment Balance", col_names = c("Name", "Fiscal Year", "% Change")) %>%
  scroll_box(height = "450px")

pct_change_ds %>%
  filter(pct_change != Inf) %>%
  select(organization_name, fiscal_year, pct_change) %>%
  group_by(fiscal_year) %>%
  summarize(total = n(),
            avg = mean(pct_change),
            med = median(pct_change),
            sd = sd(pct_change)) %>%
  make_table(title = "Percentage of Change in Endowment Balance By Year", col_names = c("Fiscal Year", "Total Companies", "Average % Change", "Median % Change", "Standard Deviation")) %>%
  scroll_box(height = "450px")

# Basic summary stats
pct_change_inv_ds %>%   
  filter(!is.na(pct_change) & pct_change != Inf) %>%
  summarize(avg_pct_change = mean(pct_change),
            median_pct_change = median(pct_change),
            sd_pct_change = sd(pct_change))

pct_change_inv_ds %>%   
  filter(!is.na(pct_change) & pct_change != Inf) %>%
  group_by(EIN) %>%
  summarize(avg_pct_change = mean(pct_change),
            median_pct_change = median(pct_change),
            sd_pct_change = sd(pct_change))

# Basic histogram summarizing it
ggplot(pct_change_inv_ds, aes(x = pct_change)) +
  geom_histogram(binwidth = 20) + 
  xlab("% Change\n(EYB - INV - BYB) / BYB * 100") + 
  ggtitle(label = "Percentage of Change in Endowment Balance", subtitle = "Red Line indicates 100%") +
  theme_classic() + 
  geom_vline(xintercept = 100, color = "maroon", linetype = "dotted")

## Histogram with cutoff of 200%
ggplot(pct_change_inv_ds, aes(x = pct_change)) +
  geom_histogram(binwidth = 5) + 
  xlab("% Change\n(EYB - BYB) / BYB * 100") + 
  xlim(-100, 200) + 
  ggtitle(label = "Percentage of Change in Endowment Balance") +
  theme_classic()

pct_change_inv_ds %>%
  filter(pct_change != Inf) %>%
  select(organization_name, fiscal_year, pct_change) %>%
  make_table(title = "Percentage of Change in Endowment Balance", col_names = c("Name", "Fiscal Year", "% Change")) %>%
  scroll_box(height = "450px")

pct_change_inv_ds %>%
  filter(pct_change != Inf) %>%
  select(organization_name, fiscal_year, pct_change) %>%
  group_by(fiscal_year) %>%
  summarize(total = n(),
            avg = mean(pct_change),
            med = median(pct_change),
            sd = sd(pct_change)) %>%
  make_table(title = "Percentage of Change in Endowment Balance By Year", col_names = c("Fiscal Year", "Total Companies", "Average % Change", "Median % Change", "Standard Deviation")) %>%
  scroll_box(height = "450px")

## Ranges of different percent change  
# Reordering by standard deviation of pct_spend down
pct_change_ds_box <- pct_change_ds %>%
  group_by(organization_name) %>%
  filter(pct_change != Inf) %>%
  summarize(sd = sd(pct_change, na.rm = TRUE)) %>%
  right_join(pct_change_ds, by = "organization_name") %>%
  select(organization_name, EIN, pct_change, sd) %>%
  mutate(organization_name = reorder(organization_name, -sd, na.rm = TRUE))

## Unlimited 
box_plot <- ggplot(pct_change_ds_box, aes(x = organization_name, y = pct_change)) + 
  geom_boxplot(aes(color = organization_name), show.legend = FALSE) + 
  geom_point(size = 1, alpha = 0.5, show.legend = FALSE) + 
  theme_bw() + 
  labs(title = "Range of Endowment Percent Change per Company",
       x = "Dance Company",
       y = "Percentage of Change in Endowment Balance") + 
  theme(axis.text.x = element_blank()) + 
  geom_hline(yintercept = 100, linetype = "dotted", color = "maroon") + 
  scale_colour_manual(values = pal)
ggplotly(box_plot) %>%
  layout(showlegend = FALSE)

##Limited to 100 for visibility 
box_plot_lim <- ggplot(pct_change_ds_box, aes(x = organization_name, y = pct_change)) + 
  geom_boxplot(aes(color = organization_name), show.legend = FALSE) + 
  geom_point(size = 1, alpha = 0.5) + 
  theme_bw() + 
  labs(title = "Range of Endowment % Change (Max of 100) per Company",
       x = "Dance Company",
       y = "Percentage of Change in Endowment Balance") + 
  theme(axis.text.x = element_blank()) + 
  scale_y_continuous(breaks = scales::breaks_pretty(n = 20),
                     limit = c(-100,100)) + 
   scale_colour_manual(values = pal)
box_plot_lim

ggplotly(box_plot_lim)  %>%
  layout(showlegend = FALSE)

## Ranges of different percent change  
# reorder(organization_name, pull(summarize(spend_down, sd = sd(group_by(spend_down, pct_change)))), na.rm = TRUE)
# Reordering by standard deviation of pct_spend down
pct_change_inv_box <- pct_change_inv_ds %>%
  group_by(organization_name) %>%
  filter(pct_change != Inf) %>%
  summarize(sd = sd(pct_change, na.rm = TRUE)) %>%
  right_join(pct_change_ds, by = "organization_name") %>%
  select(organization_name, EIN, pct_change, sd) %>%
  mutate(organization_name = reorder(organization_name, -sd, na.rm = TRUE))

## Unlimited 
box_plot_inv <- ggplot(pct_change_inv_box, aes(x = organization_name, y = pct_change)) + 
  geom_boxplot(aes(color = organization_name), show.legend = FALSE) + 
  geom_point(size = 1, alpha = 0.5) + 
  theme_bw() + 
  labs(title = "Range of Endowment Percent Change per Company",
       x = "Dance Company",
       y = "Percentage of Change in Endowment Balance") + 
  theme(axis.text.x = element_blank()) + 
  geom_hline(yintercept = 100, linetype = "dotted", color = "maroon") + 
  scale_colour_manual(values = pal)
ggplotly(box_plot_inv) %>%
  layout(showlegend = FALSE)

##Limited to 100 for visibility 
box_plot_inv_lim <- ggplot(pct_change_inv_box, aes(x = organization_name, y = pct_change)) + 
  geom_boxplot(aes(color = organization_name), show.legend = FALSE) + 
  geom_point(size = 1, alpha = 0.5) + 
  theme_bw() + 
  labs(title = "Range of Endowment % Change (Max of 100) per Company",
       x = "Dance Company",
       y = "Percentage of Change in Endowment Balance") + 
  theme(axis.text.x = element_blank()) + 
  scale_y_continuous(breaks = scales::breaks_pretty(n = 20),
                     limit = c(-100,100)) + 
  scale_colour_manual(values = pal)
ggplotly(box_plot_inv_lim)  %>%
  layout(showlegend = FALSE)

## Retrieving median values  
pct_change_med <- pct_change_ds %>%
  group_by(fiscal_year) %>%
  summarize(median = median(pct_change, na.rm = TRUE)) %>%
  mutate(organization_name = "Median",
         fiscal_year_dt = paste(fiscal_year, "-01-01", sep = ""))
pct_change_med$fiscal_year_dt = as.Date(pct_change_med$fiscal_year_dt, format = "%Y-%m-%d")

## Spend Down over Time
pct_change_plot <- pct_change_ds %>%
  filter(pct_change != Inf) %>%
  ggplot(aes(x = fiscal_year, y = pct_change, 
             group = organization_name, color = organization_name)) +
  geom_line(alpha = 0.5, show.legend =FALSE) + 
  theme_bw() + 
  scale_colour_manual(values = pal) + 
  labs(y = "Percent Change",
       x = "Fiscal Year",
       title = "Percentage of Change in Endowment Balance",
       subtitle = "By Fiscal Year") + 
  theme(plot.title = element_text(size = 10, face = "bold", hjust = .5),
        axis.title = element_text(size = 12, face = "bold"),
        plot.subtitle = element_text(size = 5, face = "italic", hjust = .5),
        axis.text.x = element_text(size = 10, angle = 25),
        strip.text = element_text(face="bold",size = 5),
        legend.key.size = unit(1, 'mm'),
        legend.text = element_text(size=7)) + 
  scale_y_continuous(labels = scales::comma_format(),
                     breaks = scales::pretty_breaks(n = 20)) +
  geom_hline(yintercept = 100, linetype = "dotted", color = "gray") +
  geom_point(data = pct_change_med, aes(x = fiscal_year, y = median), color = "black", size = 1)
pct_change_plot

ggplotly(pct_change_plot) 

##Plot with Y scale between -100 and 100 
limited_scale <- pct_change_ds %>%
  filter(pct_change != Inf) %>%
  ggplot(aes(x = fiscal_year, y = pct_change, 
             group = organization_name, color = organization_name)) +
  geom_line(show.legend = FALSE, alpha = 0.5) + 
  theme_bw() + 
  scale_colour_manual(values = pal) + 
  labs(y = "Percent Change",
       x = "Fiscal Year",
       title = "Percentage of Change in Endowment Balance (max 100)",
       subtitle = "By Fiscal Year") + 
  theme(plot.title = element_text(size = 10, face = "bold", hjust = .5),
        axis.title = element_text(size = 12, face = "bold"),
        plot.subtitle = element_text(size = 5, face = "italic", hjust = .5),
        axis.text.x = element_text(size = 10, angle = 25),
        strip.text = element_text(face="bold",size = 5),
        legend.key.size = unit(1, 'mm'),
        legend.text = element_text(size=7)) + 
  scale_y_continuous(labels = scales::comma_format(),
                     breaks = scales::pretty_breaks(n = 20),
                     limits = c(-100, 100))  +
  geom_point(data = pct_change_med, aes(x = fiscal_year, y = median), color = "black", size = 1)
limited_scale

ggplotly(limited_scale)

## Retrieving median values  
pct_change_inv_med <- pct_change_inv_ds %>%
  group_by(fiscal_year) %>%
  summarize(median = median(pct_change, na.rm = TRUE)) %>%
  mutate(organization_name = "Median")

## Spend Down over Time
pct_change_inv_plot <- pct_change_inv_ds %>%
  filter(pct_change != Inf) %>%
  ggplot(aes(x = fiscal_year, y = pct_change, 
             group = organization_name, color = organization_name)) +
  geom_line(alpha = 0.5, show.legend = FALSE) + 
  scale_colour_manual(values = pal) + 
  theme_bw() + 
  labs(y = "Percent Change",
       x = "Fiscal Year",
       title = "Percentage of Change in Endowment Balance",
       subtitle = "By Fiscal Year") + 
  theme(plot.title = element_text(size = 10, face = "bold", hjust = .5),
        axis.title = element_text(size = 12, face = "bold"),
        plot.subtitle = element_text(size = 5, face = "italic", hjust = .5),
        axis.text.x = element_text(size = 10, angle = 25),
        strip.text = element_text(face="bold",size = 5),
        legend.key.size = unit(1, 'mm'),
        legend.text = element_text(size=7)) + 
  scale_y_continuous(labels = scales::comma_format(),
                     breaks = scales::pretty_breaks(n = 20)) +
  geom_hline(yintercept = 100, linetype = "dotted", color = "gray")  +
  geom_point(data = pct_change_inv_med, aes(x = fiscal_year, y = median), color = "black", size = 1)
pct_change_inv_plot

ggplotly(pct_change_inv_plot) 

##Plot with Y scale between -100 and 100 
limited_scale_inv <- pct_change_inv_ds %>%
  filter(pct_change != Inf) %>%
  ggplot(aes(x = fiscal_year, y = pct_change, 
             group = organization_name, color = organization_name)) +
  geom_line(show.legend = FALSE, alpha = 0.5) + 
  scale_colour_manual(values = pal) + 
  theme_bw() + 
  labs(y = "Percent Change",
       x = "Fiscal Year",
       title = "Percentage of Change in Endowment Balance (max 100)",
       subtitle = "By Fiscal Year") + 
  theme(plot.title = element_text(size = 10, face = "bold", hjust = .5),
        axis.title = element_text(size = 12, face = "bold"),
        plot.subtitle = element_text(size = 5, face = "italic", hjust = .5),
        axis.text.x = element_text(size = 10, angle = 25),
        strip.text = element_text(face="bold",size = 5),
        legend.key.size = unit(1, 'mm'),
        legend.text = element_text(size=7)) + 
  scale_y_continuous(labels = scales::comma_format(),
                     breaks = scales::pretty_breaks(n = 20),
                     limits = c(-100, 100))  +
  geom_point(data = pct_change_inv_med, aes(x = fiscal_year, y = median), color = "black", size = 0.5)
limited_scale_inv

ggplotly(limited_scale_inv)

## GETTING PERCENT CHANGE OF FISCAL YEARS THAT EXIST IN THE DATASET
## Year wise PCT change for S&P 500 
sp_end <- read_csv(here('data','SP500.csv')) %>%
  rename_with(tolower) %>%
  mutate(sp500 = as.numeric(sp500)) %>%
  filter(!is.na(sp500)) %>%
  select(date_end = date, sp500_end = sp500) %>%
  mutate(month = month(date_end),
         year = year(date_end),
         # Weekend / National holiday detection 
         date_last = case_when( 
           wday(date_end - 365) == as.factor("1") ~ date_end - 367, #Sunday
           wday(date_end - 365) == as.factor("7") ~ date_end - 366, #Saturday
           TRUE ~ date_end - 365
           ))
## All 'starting' fiscal year values 
sp_start <- read_csv(here('data','SP500.csv')) %>%
  rename_with(tolower) %>%
  mutate(sp500 = as.numeric(sp500)) %>%
  filter(!is.na(sp500)) %>%
  select(date_start = date, sp500_start = sp500) %>%
  mutate(month = month(date_start),
         year = year(date_start))

#Change over the fiscal year
sp_change_years <- sp_end %>%
  left_join(sp_start, by = c("date_last" = "date_start")) %>%
  mutate(pct_change_yr = (sp500_end - sp500_start)/sp500_start * 100)

## Spend Down over Time  
## With year-wise pct change for S&P 500
pct_change_rtd %>%
  filter(pct_change != Inf) %>%
  ggplot(aes(x = TaxPeriodEndDt, y = pct_change)) +
  geom_line(aes(group = organization_name, color = organization_name), alpha = 0.5, show.legend = FALSE) + 
  theme_bw() + 
  labs(y = "Percent Change",
       x = "Fiscal Year",
       title = "Percentage of Change in Endowment Balance",
       subtitle = "Black Dot: Median. Black line: S&P500 % change in a month") + 
  theme(plot.title = element_text(size = 10, face = "bold", hjust = .5),
        axis.title = element_text(size = 12, face = "bold"),
        plot.subtitle = element_text(size = 5, face = "italic", hjust = .5),
        axis.text.x = element_text(size = 10, angle = 25),
        strip.text = element_text(face="bold",size = 5),
        legend.key.size = unit(1, 'mm'),
        legend.text = element_text(size=7)) + 
  scale_y_continuous(labels = scales::comma_format(),
                     breaks = scales::pretty_breaks(n = 10),
                     limits = c(-100, 100)) +
  scale_color_manual(values=pal) + 
  scale_x_date(breaks = scales::pretty_breaks(n = 20)) +
  geom_point(data = pct_change_med, aes(x = fiscal_year_dt, y = median), color = "black", size = 1) + 
  geom_line(data = sp_change_years, aes(x = date_end, y = pct_change_yr), color = "black", show.legend = FALSE)

pct_change_rtd %>%
  filter(pct_change != Inf) %>%
  left_join(sp_change_years, by = c("TaxPeriodEndDt" = "date_end")) %>%
  select(organization_name, TaxPeriodEndDt, pct_change, pct_change_yr) %>%
  arrange(desc(abs(pct_change))) %>%
  make_table(title = "Percentage Change of Endowment and S&P 500", 
             col_names = c("Organization Name", "Fiscal Year End Date", "Endowment Percent Change", "S&P 500 Percent Change")) %>%
  scroll_box(height = "450px")